package com.wangm.easyExcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.util.StringUtils;
import com.wangm.easyExcel.converter.CustomsIntegerNumberConverter;
import com.wangm.easyExcel.listener.CheckImportListener;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.stream.Collectors;

/**
 * excel导入处理器,在easyExcel基础封装,增加通用读取、校验功能
 *
 * @author wangmeng
 * @since 2024/6/7
 */
@Setter
@Getter
@Accessors(chain = true)
@Slf4j
public class ExcelImportProcessor {

    /**
     * 默认校验类型listener
     */
    private CheckImportListener<?> listener = new CheckImportListener<>();


    private Consumer<ExcelReaderBuilder> readerBuilderConsumer;

    /**
     * 默认第一个sheet
     */
    private Integer sheetNo = 0;

    /**
     * 错误列名
     */
    private final static String ERROR_COLUMN_NAME = "错误信息";


    public ExcelImportProcessor() {

    }

    public ExcelImportProcessor(CheckImportListener<?> listener) {
        this.listener = listener;
    }


    public <R> List<R> importData(MultipartFile file, Class<R> clazz) {
        // 校验文件
        validateExcel(file);
        try {
            return importData(file.getInputStream(), clazz);
        } catch (IOException e) {
            log.info("导入失败,异常,", e);
            throw new RuntimeException("导入失败!");
        }
    }


    public <R> List<R> importData(InputStream inputStream, Class<R> clazz) {
        List<R> dataList = null;
        try {
            ExcelReaderBuilder readerBuilder = EasyExcel.read(inputStream, clazz, listener);
            if (readerBuilderConsumer != null) {
                readerBuilderConsumer.accept(readerBuilder);
            }
            ExcelReaderSheetBuilder sheetBuilder = readerBuilder.sheet(sheetNo);
            // 控制小数转integer时抛异常
            sheetBuilder.registerConverter(CustomsIntegerNumberConverter.getInstance());
            dataList = sheetBuilder.doReadSync();
        } catch (ExcelDataConvertException exception) {
            List<ExcelErrorMessage> errorList = listener.getErrorList();
            String headerName = exception.getExcelContentProperty().getField().getAnnotation(ExcelProperty.class).value()[0];
            errorList.add(new ExcelErrorMessage().setRowNum(exception.getRowIndex() + 1)
                    .setMessage("'" + headerName + "'类型转换失败,请输入正确格式"));
            throw exception;
        }
        if (CollectionUtils.isEmpty(dataList)) {
            throw new RuntimeException("解析数据为空!");
        }
        return dataList;
    }

    public List<ExcelErrorMessage> getErrorList() {
        return listener.getErrorList();
    }


    /**
     * 手动添加错误
     *
     * @param index        行
     * @param errorMessage 错误信息
     */
    public void addError(Integer index, String errorMessage) {
        listener.addError(index, errorMessage);
    }

    /**
     * 生成错误信息excel,在原excel文件追加错误列
     *
     * @param filePath 源文件路径
     * @param outPath 输出路径
     */
    public Boolean generateErrorSheet(String filePath,String outPath) {
        List<ExcelErrorMessage> errorList = listener.getErrorList();
        if (CollectionUtils.isEmpty(errorList)) {
            return false;
        }
        Map<Integer, String> errorMap = errorList.stream().collect(Collectors.groupingBy(ExcelErrorMessage::getRowNum,
                Collectors.mapping(ExcelErrorMessage::getMessage, Collectors.joining(";"))));

        Workbook workbook = null;
        // 打开原excel文件
        try (
                FileInputStream inputStream = new FileInputStream(filePath)) {
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(sheetNo);

            // 添加错误列
            Row headerRow = sheet.getRow(0);
            short lastCellNum = headerRow.getLastCellNum();
            // 检查是否已经存在错误列
            Cell lastValidCell = headerRow.getCell(lastCellNum - 1);
            if (lastValidCell != null) {
                if (!ERROR_COLUMN_NAME.equals(lastValidCell.getStringCellValue())) {
                    Cell errorHeaderCell = headerRow.createCell(lastCellNum);
                    errorHeaderCell.setCellValue(ERROR_COLUMN_NAME);
                    errorMap.forEach((rowNum, msg) -> {
                        Row row = sheet.getRow(rowNum - 1);
                        if (row != null) {
                            Cell errorCell = row.createCell(lastCellNum);
                            errorCell.setCellValue(msg);
                        }
                    });

                } else {
                    errorMap.forEach((rowNum, msg) -> {
                        Row row = sheet.getRow(rowNum - 1);
                        if (row != null) {
                            Cell errorCell = row.getCell(lastCellNum - 1);
                            errorCell.setCellValue(msg);
                        }
                    });
                }
            }


        } catch (IOException e) {
            log.error("生成错误信息失败,", e);
            throw new RuntimeException("生成错误信息失败");
        }

        try (FileOutputStream outputStream = new FileOutputStream(outPath)) {
            // 写回去
            workbook.write(outputStream);
            workbook.close();
        } catch (IOException e) {
            log.error("生成错误信息失败,", e);
            throw new RuntimeException("生成错误信息失败");
        }
        return true;
    }


    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     *
     * @param file
     * @return
     */
    public static void validateExcel(MultipartFile file) {
        if (file == null) {
            throw new RuntimeException("文件为空!");
        }
        String fileName = file.getOriginalFilename();
        if (fileName != null && !isExcel2007(fileName)) {
            throw new RuntimeException("文件必须是excel格式！");
        }
        if (StringUtils.isEmpty(fileName) || file.getSize() == 0) {
            throw new RuntimeException("文件内容不能为空");
        }
    }


}
